﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

using Bpms.Data;
using Bpms.Entity;
using Bpms.Log;
using Bpms.Common;

namespace Bpms.Data.SqlServer
{
    public partial class DataProvider : IDataProvider
    {
        #region Private

        // 当前文件对应的逻辑类名，用于写日志
        string userClassName = "Bpms.Data.SqlServer.User";

        /// <summary>
        /// 绑定数据到用户实体
        /// </summary>
        /// <param name="idr">idatareader</param>
        /// <returns>IList<UserInfo>对象</returns>
        IList<UserInfo> GetDataBindedUser(IDataReader idr)
        {
            IList<UserInfo> userList = new List<UserInfo>();

            while (idr.Read())
            {
                UserInfo user = new UserInfo();

                // 基本信息
                user.UserID = int.Parse(idr["user_id"].ToString());         // 用户的UserID
                user.Email = idr["email"].ToString();                       // E-mail
                user.OrganizationID = int.Parse(idr["organization_id"].ToString()); // 所属团队ID
                user.JobTitleID = int.Parse(idr["job_title_id"].ToString());// 职位ID
                user.ChName = idr["ch_name"].ToString();                    // 中文名称
                user.EnName = idr["en_name"].ToString();                    // 英文名称
                user.Gender = byte.Parse(idr["gender"].ToString());         // 性别
                user.Extension = idr["extension"].ToString();               // 分机
                user.Mobile = idr["mobile"].ToString();                     // 手机
                user.Remark = idr["remark"].ToString();                     // 备注

                // 扩展信息
                user.Password = idr["password"].ToString();                 // 密码
                user.IsEnabled = byte.Parse(idr["is_enabled"].ToString());  // 状态（0：注销，1：启动）
                user.IsDelete = Convert.ToBoolean(idr["is_delete"]);    // 是否删除（false：未删除，true：已删除）
                user.RoleID = int.Parse(idr["role_id"].ToString());         // 角色ID
                user.LastLoginTime = TimeFormat.Time_T2DateTime(Convert.ToInt64(idr["last_login_time"]));  // 最后登录时间
                user.LastLoginIP = idr["last_login_ip"].ToString();         // 最后登录IP
                user.Interval = byte.Parse(idr["interval"].ToString());     // 接收消息的时间间隔（默认5分钟）

                // 其他信息
                user.UserCustomers = GetUserCustomers(int.Parse(idr["user_id"].ToString()));       // 为该用户分配的客户

                userList.Add(user);
            }

            return userList;
        }

        /// <summary>
        /// 根据sql语句返回用户信息
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>用户对象</returns>
        UserInfo GetUserBySQL(string sql)
        {
            IList<UserInfo> userList = GetUserListBySQL(sql);

            if (userList.Count > 0)
            {
                return userList[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 根据sql语句返回用户列表
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns> IList<UserInfo>对象</returns>
        IList<UserInfo> GetUserListBySQL(string sql)
        {
            IList<UserInfo> userList = new List<UserInfo>();

            using (IDataReader idr = DbHelper.ExecuteReader(CommandType.Text, sql) as IDataReader)
            {
                try
                {
                    userList = GetDataBindedUser(idr);
                }
                catch (Exception e)
                {
                    log.WriteLog(this.userClassName + "：获取绑定的用户信息时出现异常", e);
                }
                finally
                {
                    if (idr != null)
                    {
                        idr.Close();
                    }
                }
            }

            return userList;
        }

        #endregion

        /// <summary>
        /// 添加用户
        /// </summary>
        /// <param name="user">用户实体对象</param>
        /// <returns>添加是否成功（true：成功，false：失败）</returns>
        public bool InsertUser(UserInfo user)
        {
            bool isExecSuccessed = false;

            // 设置用户的默认密码为邮箱的用户名
            string password = user.Email.Substring(0, user.Email.IndexOf("@") - 1);

            try
            {
                DbParameter[] parms = 
                {
                    // 基本信息
                    DbHelper.MakeInParam("@user_id", (DbType)SqlDbType.Int, 4, user.UserID),
                    DbHelper.MakeInParam("@email", (DbType)SqlDbType.VarChar, 50, user.Email),  // 用户的E-mail，用户名
                    DbHelper.MakeInParam("@organization_id", (DbType)SqlDbType.Int, 4, user.OrganizationID),        // 所属团队ID
                    DbHelper.MakeInParam("@job_title_id", (DbType)SqlDbType.Int, 4, user.JobTitleID),               // 职位ID
                    DbHelper.MakeInParam("@ch_name", (DbType)SqlDbType.NVarChar, 30, user.ChName),                  // 中文名
                    DbHelper.MakeInParam("@en_name", (DbType)SqlDbType.VarChar, 30, user.EnName),                   // 英文名
                    DbHelper.MakeInParam("@gender", (DbType)SqlDbType.TinyInt, 1, user.Gender),                     // 性别（1：男，2：女）
                    DbHelper.MakeInParam("@extension", (DbType)SqlDbType.VarChar, 10, user.Extension),              // 分机
                    DbHelper.MakeInParam("@mobile", (DbType)SqlDbType.VarChar, 15, user.Mobile),                    // 手机
                    DbHelper.MakeInParam("@remark", (DbType)SqlDbType.NVarChar, 200, user.Remark),                  // 备注

                    // 扩展信息
                    DbHelper.MakeInParam("@password", (DbType)SqlDbType.VarChar, 32, Security.GenerateMD5Code(password)),  //密码
                    DbHelper.MakeInParam("@is_enabled", (DbType)SqlDbType.TinyInt, 1, user.IsEnabled),              // 状态（0：注销，1：启动）
                    DbHelper.MakeInParam("@is_delete", (DbType)SqlDbType.TinyInt, 1, user),                         // 公司名称
                    DbHelper.MakeInParam("@role_id", (DbType)SqlDbType.Int, 4, user.RoleID),                        // 角色ID
                    DbHelper.MakeInParam("@last_login_time", (DbType)SqlDbType.BigInt, 8, TimeFormat.DateTime2Time_T(user.LastLoginTime)),  // 最后登录时间
                    DbHelper.MakeInParam("@last_login_ip", (DbType)SqlDbType.VarChar, 15, user.LastLoginIP),        // 最后登录IP
                    DbHelper.MakeInParam("@interval", (DbType)SqlDbType.TinyInt, 1, user.Interval),                 // 接收消息的时间间隔（默认5分钟）

                    // 存储过程返回值
                    DbHelper.MakeOutParam("@is_exec_succeed", (DbType)SqlDbType.Bit, 1)                             // 判断是否执行成功的返回值
                };

                DbHelper.ExecuteNonQuery(CommandType.StoredProcedure, "bpms_add_user", parms);

                if (bool.Parse(parms[12].Value.ToString()))
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                log.WriteLog(this.userClassName + "：添加用户信息时出现异常", e);
            }

            return isExecSuccessed;
        }

        /// <summary>
        /// 修改用户信息
        /// </summary>
        /// <param name="user">用户实例</param>
        /// <returns>判断操作是否成功（true：成功，false：失败）</returns>
        public bool UpdateUser(UserInfo user)
        {
            bool isExecSuccessed = false;

            try
            {
                DbParameter[] parms = 
                {
                    // 基本信息
                    DbHelper.MakeInParam("@user_id", (DbType)SqlDbType.Int, 4, user.UserID),
                    DbHelper.MakeInParam("@organization_id", (DbType)SqlDbType.Int, 4, user.OrganizationID),        //所属团队ID
                    DbHelper.MakeInParam("@job_title_id", (DbType)SqlDbType.Int, 4, user.JobTitleID),               //职位ID
                    DbHelper.MakeInParam("@ch_name", (DbType)SqlDbType.NVarChar, 30, user.ChName),                  //中文名
                    DbHelper.MakeInParam("@en_name", (DbType)SqlDbType.VarChar, 30, user.EnName),                   //英文名
                    DbHelper.MakeInParam("@gender", (DbType)SqlDbType.TinyInt, 1, user.Gender),                     //性别（1：男，2：女）
                    DbHelper.MakeInParam("@extension", (DbType)SqlDbType.VarChar, 10, user.Extension),              //分机
                    DbHelper.MakeInParam("@mobile", (DbType)SqlDbType.VarChar, 15, user.Mobile),                    //手机
                    DbHelper.MakeInParam("@remark", (DbType)SqlDbType.NVarChar, 200, user.Remark),                   //备注

                    // 存储过程返回值
                    DbHelper.MakeOutParam("@is_exec_succeed", (DbType)SqlDbType.Bit, 1)                             // 判断是否执行成功的返回值
                };

                DbHelper.ExecuteNonQuery(CommandType.StoredProcedure, "bpms_update_user", parms);

                if (bool.Parse(parms[12].Value.ToString()))
                {
                    isExecSuccessed = true;
                }
            }
            catch (Exception e)
            {
                log.WriteLog(this.userClassName + "：修改用户信息时出现异常", e);
            }

            return isExecSuccessed;
        }

        /// <summary>
        /// 删除用户
        /// </summary>
        /// <param name="userId">用户的UserID</param>
        /// <returns>操作是否成功（true：成功，false：失败）</returns>
        public bool DeleteUser(int userId)
        {
            bool isExecSuccessed = false;

            try
            {
                DbParameter[] parms = 
                {
                    DbHelper.MakeInParam("@user_id", (DbType)SqlDbType.Int, 4, userId),          // 用户的UserID
                    DbHelper.MakeOutParam("@is_exec_succeed", (DbType)SqlDbType.Bit, 1)          // 判断是否执行成功的返回值
                };
                if (DbHelper.ExecuteNonQuery(CommandType.StoredProcedure, "bpms_delete_user", parms) > 0)
                {
                    isExecSuccessed = false;
                }
            }
            catch (Exception e)
            {
                log.WriteLog(this.userClassName, e);
            }

            return isExecSuccessed;
        }

        /// <summary>
        /// 判断用户是否存在
        /// </summary>
        /// <param name="user">用户实体对象</param>
        /// <param name="type">判断的类型（添加或修改）</param>
        /// <returns>用户是否存在（true：已存在，false：未存在）</returns>
        public bool UserExists(UserInfo user, OperateType type)
        {
            StringBuilder sbSql = new StringBuilder("SELECT 1 FROM bp_user WHERE ");
            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@user_id", (DbType)SqlDbType.Int, 4, user.UserID), 
                DbHelper.MakeInParam("@email", (DbType)SqlDbType.VarChar, 50, user.Email) 
            };

            switch (type)
            {
                case OperateType.Insert:
                    sbSql.AppendFormat(" [email] = @email ");
                    break;
                case OperateType.Update:
                    sbSql.AppendFormat(" [email] = @email AND [user_id] <> @user_id ");
                    break;
                default:
                    log.WriteLog(this.userClassName + "：检测用户E-mail是否存在，参数错误，未指定检测类型，将按照已经存在处理，防止添加新的同名E-mail");
                    sbSql.AppendFormat(" 1 = 2 ");
                    break;
            }
            try
            {
                if (DbHelper.ExecuteScalar(CommandType.Text, sbSql.ToString(), parms) != null)
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                log.WriteLog(this.userClassName + "：判断用户E-mail是否存在时产生异常", e);
            }

            return false;
        }

        /// <summary>
        /// 得到用户的基本信息
        /// </summary>
        /// <param name="userId">用户的UserID</param>
        /// <returns>用户实体对象</returns>
        public UserInfo GetUser(int userId)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(" SELECT bp_user.[user_id], bp_user.email, bp_user.organization_id, bp_user.job_title_id, ");
            sbSql.Append(" bp_user.ch_name, bp_user.en_name, bp_user.gender, bp_user.extension, bp_user.mobile, ");
            sbSql.Append(" bp_user.remark, bp_user_extension.[password], bp_user_extension.is_enabled, ");
            sbSql.Append(" bp_user_extension.is_delete, bp_user_extension.role_id, bp_user_extension.last_login_time, ");
            sbSql.Append(" bp_user_extension.last_login_ip, bp_user_extension.[interval] ");
            sbSql.Append(" FROM bp_user INNER JOIN bp_user_extension ON bp_user.[user_id] = bp_user_extension.[user_id] ");
            sbSql.Append(" WHERE bp_user_extension.is_delete = 0 AND bp_user.[user_id] = ").Append(userId);

            return GetUserBySQL(sbSql.ToString());
        }

        /// <summary>
        /// 得到用户的基本信息
        /// </summary>
        /// <param name="email">用户的E-mail（用户名）</param>
        /// <returns>用户实体对象</returns>
        public UserInfo GetUser(string email)
        {
            int userId = GetUserIDByEmail(email);
            return GetUser(userId);
        }

        /// <summary>
        /// 提取用户列表
        /// </summary>
        /// <param name="pageId">当前页</param>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="condition">提取条件</param>
        /// <returns>用户列表</returns>
        public IList<UserInfo> GetUsers(int pageId, int pageSize, string condition)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(" SELECT TOP {0} bp_user.[user_id], bp_user.email, bp_user.organization_id, bp_user.job_title_id, ");
            sbSql.Append(" bp_user.ch_name, bp_user.en_name, bp_user.gender, bp_user.extension, bp_user.mobile, ");
            sbSql.Append(" bp_user.remark, bp_user_extension.[password], bp_user_extension.is_enabled, ");
            sbSql.Append(" bp_user_extension.is_delete, bp_user_extension.role_id, bp_user_extension.last_login_time, ");
            sbSql.Append(" bp_user_extension.last_login_ip, bp_user_extension.[interval] ");
            sbSql.Append(" FROM bp_user INNER JOIN bp_user_extension ON bp_user.[user_id] = bp_user_extension.[user_id] ");
            sbSql.Append(" WHERE {2} AND bp_user_extension.is_delete = 0 AND bp_user.[user_id] NOT IN ");
            sbSql.Append(" ( ");
            sbSql.Append("      SELECT TOP {1} bp_user.[user_id] FROM bp_user ");
            sbSql.Append("       INNER JOIN bp_user_extension ON bp_user.[user_id] = bp_user_extension.[user_id] ");
            sbSql.Append("       WHERE {2} AND bp_user_extension.is_delete = 0 ");
            sbSql.Append("       ORDER BY bp_user.[user_id] DESC ");
            sbSql.Append(" ) ");
            sbSql.Append(" ORDER BY bp_user.[user_id] DESC ");

            string sql = sbSql.ToString();
            sbSql.Remove(0, sbSql.Length);
            sbSql.AppendFormat(sql, pageSize, pageSize * (pageId - 1), condition);

            return GetUserListBySQL(sbSql.ToString());
        }

        /// <summary>
        /// 得到满足条件的记录数
        /// </summary>
        /// <param name="condition">条件</param>
        /// <returns>满足条件的记录数</returns>
        public int GetUserCount(string condition)
        {
            StringBuilder sbSql = new StringBuilder();

            sbSql.Append(" SELECT COUNT(*) AS cnt ");
            sbSql.Append(" FROM bp_user INNER JOIN bp_user_extension ON bp_user.[user_id] = bp_user_extension.[user_id] ");
            if (condition.Trim() != "")
            {
                sbSql.Append(" WHERE ").Append(condition);
            }
            try
            {
                return (int)DbHelper.ExecuteScalar(CommandType.Text, sbSql.ToString(), null);
            }
            catch (Exception e)
            {
                log.WriteLog(this.userClassName + "：得到满足条件的记录数时出现异常", e);
                return 0;
            }
        }

        /// <summary>
        /// 验证用户登录
        /// </summary>
        /// <param name="UserInfo">用户实体类</param>
        /// <returns>验证用户登录信息是否正确（true：正确，fales：不正确）</returns>
        public bool VerifyUser(ref UserInfo user)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(" SELECT bp_user.[user_id] ");
            sbSql.Append(" FROM bp_user INNER JOIN bp_user_extension ON bp_user.[user_id] = bp_user_extension.[user_id] ");
            sbSql.Append(" WHERE bp_user.[email] = @email AND bp_user_extension.[password] = @password AND is_delete = 0 ");

            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@email", (DbType)SqlDbType.VarChar, 50, user.Email),
                DbHelper.MakeInParam("@password", (DbType)SqlDbType.VarChar, 32, Security.GenerateMD5Code(user.Password)) 
            };

            int userId = Convert.ToInt32(DbHelper.ExecuteScalar(CommandType.Text, sbSql.ToString(), parms));
            bool isLoginSucceed = false;
            if (userId > 0)
            {
                user = GetUser(userId);

                isLoginSucceed = true;
            }
            return isLoginSucceed;
        }

        /// <summary>
        /// 启动一个用户
        /// </summary>
        /// <param name="userId">用户的UserID</param>
        /// <returns>操作是否成功，true：成功，false：失败</returns>
        public bool StartUser(int userId)
        {
            bool isExecSucceed = false;

            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(" UPDATE [bp_user_extension] SET [is_enabled] = 1 ");
            sbSql.Append(" WHERE [user_id] = @user_id ");

            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@user_id", (DbType)SqlDbType.Int, 4, userId)
            };

            try
            {
                DbHelper.ExecuteNonQuery(CommandType.Text, sbSql.ToString(), parms);
                isExecSucceed = true;
            }
            catch (Exception e)
            {
                log.WriteLog(this.userClassName + "：启动一个用户时出现异常", e);
            }

            return isExecSucceed;
        }

        /// <summary>
        /// 注销一个用户
        /// </summary>
        /// <param name="userId">用户的UserID</param>
        /// <returns>操作是否成功，true：成功，false：失败</returns>
        public bool CancelUser(int userId)
        {
            bool isExecSucceed = false;

            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(" UPDATE [bp_user_extension] SET [is_enabled] = 1 ");
            sbSql.Append(" WHERE [user_id] = @user_id ");

            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@user_id", (DbType)SqlDbType.Int, 4, userId) 
            };

            try
            {
                DbHelper.ExecuteNonQuery(CommandType.Text, sbSql.ToString(), parms);
                isExecSucceed = true;
            }
            catch (Exception e)
            {
                log.WriteLog(this.userClassName + "：注销一个用户时出现异常", e);
            }

            return isExecSucceed;
        }

        /// <summary>
        /// 修改用户密码
        /// </summary>
        /// <param name="userId">用户的UserID</param>
        /// <param name="newPassword">新密码</param>
        /// <returns>操作是否成功，true：成功，fales：失败</returns>
        public bool UpdatePassword(int userId, string newPassword)
        {
            bool isExecSucceed = false;

            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(" UPDATE [bp_user_extension] SET [password] = @password ");
            sbSql.Append(" WHERE [user_id] = @user_id ");

            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@user_id", (DbType)SqlDbType.Int,4,userId),
                DbHelper.MakeInParam("@password", (DbType)SqlDbType.VarChar, 30, newPassword)
            };

            try
            {
                DbHelper.ExecuteNonQuery(CommandType.Text, sbSql.ToString(), parms);
                isExecSucceed = true;
            }
            catch (Exception e)
            {
                log.WriteLog(this.userClassName + "：修改用户密码时出现异常", e);
            }

            return isExecSucceed;
        }

        /// <summary>
        /// 为用户分配客户
        /// </summary>
        /// <param name="userId">用户的UserID</param>
        /// <param name="customerIdString">客户的CustomerID数组</param>
        /// <returns>操作是否成功（true：成功，false：失败）</returns>
        public bool DistributeCustomer(int userId, string[] customerIdList)
        {
            bool isExecSucceed = false;

            foreach (string customerId in customerIdList)
            {
                if (!Validity.IsNumber(customerId))
                {
                    return false;
                }
            }

            using (DbConnection dbConn = DbHelper.Connection)
            {
                dbConn.Open();
                using (DbTransaction dbTrans = dbConn.BeginTransaction())
                {
                    try
                    {
                        // 删除原来分配给该用户的客户
                        string sql = "DELETE FROM bp_user_customer WHERE [user_id] = @user_id ";
                        DbParameter[] parms = 
                        { 
                            DbHelper.MakeInParam("@user_id", (DbType)SqlDbType.Int, 4, userId)
                        };
                        DbHelper.ExecuteNonQuery(dbTrans, CommandType.Text, sql, parms);
                        
                        // 为用户分配客户
                        sql = "INSERT INTO bp_user_customer([user_id], customer_id) VALUES (@user_id, @customer_id)";
                        
                        foreach (string customerId in customerIdList)
                        {
                            parms = new DbParameter[]
                            {
                                DbHelper.MakeInParam("@user_id", (DbType)SqlDbType.Int, 4, userId),
                                DbHelper.MakeInParam("@customer_id", (DbType)SqlDbType.Int, 4, int.Parse(customerId))
                            };
                            DbHelper.ExecuteNonQuery(dbTrans, CommandType.Text, sql, parms);
                        }

                        dbTrans.Commit();
                        isExecSucceed = true;
                    }
                    catch(Exception e)
                    {
                        dbTrans.Rollback();

                        log.WriteLog(this.userClassName + "：为用户分配客户时产生异常", e);
                    }                    
                }
            }

            return isExecSucceed;
        }

        /// <summary>
        /// 判断是否为用户分配了某一客户
        /// </summary>
        /// <param name="customers">为该用户分配的客户集合</param>
        /// <param name="customerId">某一客户ID</param>
        /// <returns>是否分配（true：已分配，false：未分配）</returns>
        public bool IsContainsCustomer(IList<CustomerInfo> customers, int customerId)
        {
            bool isContains = false;
            foreach (CustomerInfo customer in customers)
            {
                if (customer.CustomerID == customerId)
                {
                    isContains = true;
                    break;
                }
            }

            return isContains;
        }

        /// <summary>
        /// 设置用户角色
        /// </summary>
        /// <param name="userId">用户的UserID</param>
        /// <param name="roleId">角色ID</param>
        /// <returns>操作是否成功（true：成功，false：失败）</returns>
        public bool SetRole(int userId, int roleId)
        {
            bool isExecSucceed = false;

            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(" UPDATE [bp_user_extension] SET [role_id] = @role_id ");
            sbSql.Append(" WHERE [user_id] = @user_id ");

            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@user_id", (DbType)SqlDbType.Int,4,userId),
                DbHelper.MakeInParam("@role_id", (DbType)SqlDbType.Int, 4, roleId)
            };

            try
            {
                DbHelper.ExecuteNonQuery(CommandType.Text, sbSql.ToString(), parms);
                isExecSucceed = true;
            }
            catch (Exception e)
            {
                log.WriteLog(this.userClassName + "：设置角色时出现异常", e);
            }

            return isExecSucceed;
        }

        /// <summary>
        /// 得到为该用户分配的客户列表
        /// </summary>
        /// <param name="userId">用户的UserID</param>
        /// <returns>为该用户分配的客户列表</returns>
        public IList<CustomerInfo> GetUserCustomers(int userId)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(" SELECT [customer_id] FROM bp_user_customer WHERE [user_id] = @user_id ");
            sbSql.Append(" ORDER BY [customer_id] ASC ");

            DbParameter[] parms =
            { 
                DbHelper.MakeInParam("@user_id", (DbType)SqlDbType.Int, 4, userId)
            };
            using (DbDataReader reader = DbHelper.ExecuteReader(CommandType.Text, sbSql.ToString(), parms))
            {
                if (null == reader)
                {
                    return null;
                }
                try
                {
                    IList<CustomerInfo> customers = new List<CustomerInfo>();
                    CustomerInfo customer = null;
                    while (reader.Read())
                    {
                        customer = new CustomerInfo();
                        customer.CustomerID = (int)reader["customer_id"];
                        customers.Add(customer);
                    }
                    return customers;
                }
                catch(Exception e)
                {
                    log.WriteLog(this.userClassName + "：得到为用户分配的客户时产生异常", e);
                    return null;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                        reader.Dispose();
                    }
                }
            }
        }

        /// <summary>
        /// 根据用户的UserID，得到E-mail（用户名）
        /// </summary>
        /// <param name="userId">用户的UserID</param>
        /// <returns>用户的用户名</returns>
        public string GetEmailByUserID(int userId)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(" SELECT email FROM bp_user WHERE [user_id] = @user_id ");

            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@user_id", (DbType)SqlDbType.Int, 4, userId) 
            };
            object email = null;
            try
            {
                email = DbHelper.ExecuteScalar(CommandType.Text, sbSql.ToString(), parms) ?? "";
            }
            catch (Exception e)
            {
                log.WriteLog(this.userClassName + "：根据用户的UserID，得到E-mail（用户名）时出现异常", e);
            }

            return email.ToString();
        }

        /// <summary>
        /// 根据用户的E-mail（用户名），得到UserID
        /// </summary>
        /// <param name="email">用户的E-mail</param>
        /// <returns>用户的用户名</returns>
        public int GetUserIDByEmail(string email)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(" SELECT user_id FROM bp_user WHERE [email] = @email ");

            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@email", (DbType)SqlDbType.VarChar, 50, email) 
            };

            try
            {
                return Convert.ToInt32(DbHelper.ExecuteScalar(CommandType.Text, sbSql.ToString(), parms));
            }
            catch (Exception e)
            {
                log.WriteLog(this.userClassName + "：根据用户的E-mail（用户名），得到UserID时出现异常", e);
                return 0;
            }
        }
    }
}
